PostgreSQL 查询字符集与字符串转码

您所在的位置：网站首页 › char 字符集 › PostgreSQL 查询字符集与字符串转码

PostgreSQL 查询字符集与字符串转码

2024-01-08 17:56| 来源: 网络整理| 查看: 265

官方原文

CONVERT：https://www.postgresql.org/docs/10/functions-string.html

查询PostgreSQL字符集

select pg_encoding_to_char(encoding) from pg_database where datname = 'your_database';

这里提供2个函数

--针对pg_database表 select pg_encoding_to_char(6) --UTF8 --编号转换为字符集 select pg_char_to_encoding('UTF8') --6 --字符集转换为编号

convert(string bytea,

src_encoding name,

dest_encoding name)

bytea

Convert string to dest_encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. Conversions can be defined by CREATE CONVERSION. Also there are some predefined conversions. See Table 9-8 for available conversions.

convert(

'text_in_utf8',

'UTF8',

'LATIN1')

text_in_utf8represented in Latin-1 encoding (ISO 8859-1)

convert(string bytea,src_encoding, dest_encoding)

将字符串转换为dest_encoding。原始编码由src_encoding指定。字符串在这种编码中必须是有效的。转换可以通过创建转换来定义。还有一些预定义的转换。可用的转换见表9-8。

--For example convert('text_in_utf8','UTF8', 'LATIN1') -- value ,source code(源), target code(目标)

text_in_utf8represented in Latin-1 encoding (ISO 8859-1)

用Latin-1编码表示的text_in_utf8 (ISO 8859-1)

测试CONVERT正确性：

--把浪潮字符串由UTF8编码转为GBK编码 convert('浪潮','UTF8', 'GBK') --通过MD5值验证准确性 select md5(convert('浪潮','UTF8','GBK')) as gbk,md5('浪潮') as utf8 ---result--- gbk utf8 0dc1d920718b8e0d53940bf63ab5c64e a889ead0cee9b0adcf21310b789eab17

注：

这里最好先获取下数据库的字符集，然后再进行转码，最为安全

Table 9-8. Built-in Conversions

Conversion Name [a]Source EncodingDestination Encodingascii_to_micSQL_ASCIIMULE_INTERNALascii_to_utf8SQL_ASCIIUTF8big5_to_euc_twBIG5EUC_TWbig5_to_micBIG5MULE_INTERNALbig5_to_utf8BIG5UTF8euc_cn_to_micEUC_CNMULE_INTERNALeuc_cn_to_utf8EUC_CNUTF8euc_jp_to_micEUC_JPMULE_INTERNALeuc_jp_to_sjisEUC_JPSJISeuc_jp_to_utf8EUC_JPUTF8euc_kr_to_micEUC_KRMULE_INTERNALeuc_kr_to_utf8EUC_KRUTF8euc_tw_to_big5EUC_TWBIG5euc_tw_to_micEUC_TWMULE_INTERNALeuc_tw_to_utf8EUC_TWUTF8gb18030_to_utf8GB18030UTF8gbk_to_utf8GBKUTF8iso_8859_10_to_utf8LATIN6UTF8iso_8859_13_to_utf8LATIN7UTF8iso_8859_14_to_utf8LATIN8UTF8iso_8859_15_to_utf8LATIN9UTF8iso_8859_16_to_utf8LATIN10UTF8iso_8859_1_to_micLATIN1MULE_INTERNALiso_8859_1_to_utf8LATIN1UTF8iso_8859_2_to_micLATIN2MULE_INTERNALiso_8859_2_to_utf8LATIN2UTF8iso_8859_2_to_windows_1250LATIN2WIN1250iso_8859_3_to_micLATIN3MULE_INTERNALiso_8859_3_to_utf8LATIN3UTF8iso_8859_4_to_micLATIN4MULE_INTERNALiso_8859_4_to_utf8LATIN4UTF8iso_8859_5_to_koi8_rISO_8859_5KOI8Riso_8859_5_to_micISO_8859_5MULE_INTERNALiso_8859_5_to_utf8ISO_8859_5UTF8iso_8859_5_to_windows_1251ISO_8859_5WIN1251iso_8859_5_to_windows_866ISO_8859_5WIN866iso_8859_6_to_utf8ISO_8859_6UTF8iso_8859_7_to_utf8ISO_8859_7UTF8iso_8859_8_to_utf8ISO_8859_8UTF8iso_8859_9_to_utf8LATIN5UTF8johab_to_utf8JOHABUTF8koi8_r_to_iso_8859_5KOI8RISO_8859_5koi8_r_to_micKOI8RMULE_INTERNALkoi8_r_to_utf8KOI8RUTF8koi8_r_to_windows_1251KOI8RWIN1251koi8_r_to_windows_866KOI8RWIN866koi8_u_to_utf8KOI8UUTF8mic_to_asciiMULE_INTERNALSQL_ASCIImic_to_big5MULE_INTERNALBIG5mic_to_euc_cnMULE_INTERNALEUC_CNmic_to_euc_jpMULE_INTERNALEUC_JPmic_to_euc_krMULE_INTERNALEUC_KRmic_to_euc_twMULE_INTERNALEUC_TWmic_to_iso_8859_1MULE_INTERNALLATIN1mic_to_iso_8859_2MULE_INTERNALLATIN2mic_to_iso_8859_3MULE_INTERNALLATIN3mic_to_iso_8859_4MULE_INTERNALLATIN4mic_to_iso_8859_5MULE_INTERNALISO_8859_5mic_to_koi8_rMULE_INTERNALKOI8Rmic_to_sjisMULE_INTERNALSJISmic_to_windows_1250MULE_INTERNALWIN1250mic_to_windows_1251MULE_INTERNALWIN1251mic_to_windows_866MULE_INTERNALWIN866sjis_to_euc_jpSJISEUC_JPsjis_to_micSJISMULE_INTERNALsjis_to_utf8SJISUTF8tcvn_to_utf8WIN1258UTF8uhc_to_utf8UHCUTF8utf8_to_asciiUTF8SQL_ASCIIutf8_to_big5UTF8BIG5utf8_to_euc_cnUTF8EUC_CNutf8_to_euc_jpUTF8EUC_JPutf8_to_euc_krUTF8EUC_KRutf8_to_euc_twUTF8EUC_TWutf8_to_gb18030UTF8GB18030utf8_to_gbkUTF8GBKutf8_to_iso_8859_1UTF8LATIN1utf8_to_iso_8859_10UTF8LATIN6utf8_to_iso_8859_13UTF8LATIN7utf8_to_iso_8859_14UTF8LATIN8utf8_to_iso_8859_15UTF8LATIN9utf8_to_iso_8859_16UTF8LATIN10utf8_to_iso_8859_2UTF8LATIN2utf8_to_iso_8859_3UTF8LATIN3utf8_to_iso_8859_4UTF8LATIN4utf8_to_iso_8859_5UTF8ISO_8859_5utf8_to_iso_8859_6UTF8ISO_8859_6utf8_to_iso_8859_7UTF8ISO_8859_7utf8_to_iso_8859_8UTF8ISO_8859_8utf8_to_iso_8859_9UTF8LATIN5utf8_to_johabUTF8JOHAButf8_to_koi8_rUTF8KOI8Rutf8_to_koi8_uUTF8KOI8Uutf8_to_sjisUTF8SJISutf8_to_tcvnUTF8WIN1258utf8_to_uhcUTF8UHCutf8_to_windows_1250UTF8WIN1250utf8_to_windows_1251UTF8WIN1251utf8_to_windows_1252UTF8WIN1252utf8_to_windows_1253UTF8WIN1253utf8_to_windows_1254UTF8WIN1254utf8_to_windows_1255UTF8WIN1255utf8_to_windows_1256UTF8WIN1256utf8_to_windows_1257UTF8WIN1257utf8_to_windows_866UTF8WIN866utf8_to_windows_874UTF8WIN874windows_1250_to_iso_8859_2WIN1250LATIN2windows_1250_to_micWIN1250MULE_INTERNALwindows_1250_to_utf8WIN1250UTF8windows_1251_to_iso_8859_5WIN1251ISO_8859_5windows_1251_to_koi8_rWIN1251KOI8Rwindows_1251_to_micWIN1251MULE_INTERNALwindows_1251_to_utf8WIN1251UTF8windows_1251_to_windows_866WIN1251WIN866windows_1252_to_utf8WIN1252UTF8windows_1256_to_utf8WIN1256UTF8windows_866_to_iso_8859_5WIN866ISO_8859_5windows_866_to_koi8_rWIN866KOI8Rwindows_866_to_micWIN866MULE_INTERNALwindows_866_to_utf8WIN866UTF8windows_866_to_windows_1251WIN866WINwindows_874_to_utf8WIN874UTF8euc_jis_2004_to_utf8EUC_JIS_2004UTF8utf8_to_euc_jis_2004UTF8EUC_JIS_2004shift_jis_2004_to_utf8SHIFT_JIS_2004UTF8utf8_to_shift_jis_2004UTF8SHIFT_JIS_2004euc_jis_2004_to_shift_jis_2004EUC_JIS_2004SHIFT_JIS_2004shift_jis_2004_to_euc_jis_2004SHIFT_JIS_2004EUC_JIS_2004

23.3.1. Supported Character Sets

Table 23-1 shows the character sets available for use in PostgreSQL.

Table 23-1. PostgreSQL Character Sets

NameDescriptionLanguageServer?Bytes/CharAliasesBIG5Big FiveTraditional ChineseNo1-2WIN950, Windows950EUC_CNExtended UNIX Code-CNSimplified ChineseYes1-3 EUC_JPExtended UNIX Code-JPJapaneseYes1-3 EUC_JIS_2004Extended UNIX Code-JP, JIS X 0213JapaneseYes1-3 EUC_KRExtended UNIX Code-KRKoreanYes1-3 EUC_TWExtended UNIX Code-TWTraditional Chinese, TaiwaneseYes1-3 GB18030National StandardChineseNo1-4 GBKExtended National StandardSimplified ChineseNo1-2WIN936, Windows936ISO_8859_5ISO 8859-5, ECMA 113Latin/CyrillicYes1 ISO_8859_6ISO 8859-6, ECMA 114Latin/ArabicYes1 ISO_8859_7ISO 8859-7, ECMA 118Latin/GreekYes1 ISO_8859_8ISO 8859-8, ECMA 121Latin/HebrewYes1 JOHABJOHABKorean (Hangul)No1-3 KOI8RKOI8-RCyrillic (Russian)Yes1KOI8KOI8UKOI8-UCyrillic (Ukrainian)Yes1 LATIN1ISO 8859-1, ECMA 94Western EuropeanYes1ISO88591LATIN2ISO 8859-2, ECMA 94Central EuropeanYes1ISO88592LATIN3ISO 8859-3, ECMA 94South EuropeanYes1ISO88593LATIN4ISO 8859-4, ECMA 94North EuropeanYes1ISO88594LATIN5ISO 8859-9, ECMA 128TurkishYes1ISO88599LATIN6ISO 8859-10, ECMA 144NordicYes1ISO885910LATIN7ISO 8859-13BalticYes1ISO885913LATIN8ISO 8859-14CelticYes1ISO885914LATIN9ISO 8859-15LATIN1 with Euro and accentsYes1ISO885915LATIN10ISO 8859-16, ASRO SR 14111RomanianYes1ISO885916MULE_INTERNALMule internal codeMultilingual EmacsYes1-4 SJISShift JISJapaneseNo1-2Mskanji, ShiftJIS, WIN932, Windows932SHIFT_JIS_2004Shift JIS, JIS X 0213JapaneseNo1-2 SQL_ASCIIunspecified (see text)anyYes1 UHCUnified Hangul CodeKoreanNo1-2WIN949, Windows949UTF8Unicode, 8-bitallYes1-4UnicodeWIN866Windows CP866CyrillicYes1ALTWIN874Windows CP874ThaiYes1 WIN1250Windows CP1250Central EuropeanYes1 WIN1251Windows CP1251CyrillicYes1WINWIN1252Windows CP1252Western EuropeanYes1 WIN1253Windows CP1253GreekYes1 WIN1254Windows CP1254TurkishYes1 WIN1255Windows CP1255HebrewYes1 WIN1256Windows CP1256ArabicYes1 WIN1257Windows CP1257BalticYes1 WIN1258Windows CP1258VietnameseYes1ABC, TCVN, TCVN5712, VSCII

【本文地址】

PostgreSQL 查询字符集与字符串转码

PostgreSQL 查询字符集与字符串转码

今日新闻

推荐新闻